package com.extrace.server.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.extrace.server.dto.DataDTO;
import com.extrace.server.pojo.Express;
import com.extrace.server.pojo.ProvinceData;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import javax.persistence.criteria.CriteriaBuilder;
import java.util.List;

/**
 * Created with IntelliJ IDEA.
 * Description:
 * User: 23735
 * Date: 2024-04-14
 * Time: 15:57
 */
public interface ExpressMapper extends BaseMapper<Express> {

    @Select("SELECT status FROM express where id=#{eid}")
    Integer getStatusById(@Param("eid") String eid);

    /**
     *  根据地区查询快件
     *  注意这里面查询的是待揽收的, 因为查询的是 status =0 的快件
     */
    @Select("SELECT * FROM express WHERE SUBSTRING(id, 2, 6) = #{regionCode} and status=0")
    List<Express> selectByRegionCode(@Param("regionCode") String regionCode);

    @Select("SELECT * FROM express WHERE SUBSTRING(id, 2, 6) = #{regionCode} and status=0 and notified=0")
    List<Express> selectNewWaitCollect(String regionCode);

    @Select("select region_code from customer where id=(select sender from express where id=#{eid});")
    String getExpressStartAddressCode(String eid);
    @Select("SELECT province_code, COUNT(*) AS count\n" +
            "FROM (\n" +
            "    SELECT LEFT(start_node, 2) AS province_code\n" +
            "    FROM transpackage\n" +
            "    UNION ALL\n" +
            "    SELECT LEFT(end_node, 2)\n" +
            "    FROM transpackage\n" +
            ") AS combined\n" +
            "GROUP BY province_code\n" +
            "ORDER BY count DESC;\n")
    List<ProvinceData> getProvinceData();
}
